<?php defined('SYSPATH') or die('No direct script access.');
/**
 *
 * 采购付款单批量上传
 * @author locove
 *
 */
class Controller_Report_Hzrvip extends Controller_Admin{
    /**
     * 首页
     */
    public function action_index()
    {
        $this->title='黄宗瑞专用';
        $view=View::factory('report/hzrvip');
        if(isset($_GET['errors'])){
            $view->set('errors',$_GET['errors']);
        }
        $this->response->body($view);
    }
    /**
     * 下载模板
     */
    public function action_down_template()
    {
        $this->title='下载模版';
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $this->objReader  = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel      = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("newerp")
            ->setLastModifiedBy("newerp")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet =$objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1', 'SKU');
        $objPHPExcel->getActiveSheet()->setTitle('load');
        $objPHPExcel->setActiveSheetIndex(0);
        $day=date("m-d");
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename="'.$day.'在途库存.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
    /**
     * 导入sku查询
     */
    public function action_load()
    {
        set_time_limit(0);
        ini_set('memory_limit','600M');
        ini_set('max_allowed_packet','33M');
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $xls= $_FILES['xls']['tmp_name'];
        $objPHPExcel= PHPExcel_IOFactory::load($xls);
        $data= $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
        $num = count($data);
        $skus_execl=$skus_arr=array();

        for($i=2;$i<=$num;$i++){
            $skus_execl[$i]= strtoupper(trim($data[$i]['A']));
        }
        if($skus_execl){
            $sku_can = DB::select_array(array('sku',DB::expr("count(id) as num")))->from('purchaseorder_details')
                ->where('sku','in',$skus_execl)
                ->group_by('sku')
                ->having('num','>',1)
                ->execute('newerp')
                ->as_array(NULL,'sku');

            if($sku_can)
            {
                $sku_can_str = '';
                foreach($sku_can as $sku_can_)
                {
                    $sku_can_str .= $sku_can_str?",'".$sku_can_."'":"'".$sku_can_."'";
                }
                $sql = "select * from(SELECT pd.id,pd.purchaseorder_id,pd.sku,pd.single_price,pu.purchaseorder_date
                    FROM `purchaseorders` AS pu
                    JOIN purchaseorder_details AS pd ON pu.id = pd.purchaseorder_id
                    WHERE pd.sku
                    IN (
                    $sku_can_str
                    )
                    AND pu.`purchaseorder_date` <= '2014-12-31 23:59:59' ORDER BY pu.`purchaseorder_date` desc) temp
                    group by temp.sku";
                $sku_first = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku');

                if($sku_first)
                {
                    $sku_first_ids = '';
                    foreach($sku_first as $sku_first_)
                    {
                        $sku_first_ids .= $sku_first_ids?','.$sku_first_['id']:$sku_first_['id'];
                    }

                    $sql = "select * from(SELECT pd.id,pd.sku,pd.single_price,pu.purchaseorder_date
                    FROM `purchaseorders` AS pu
                    JOIN purchaseorder_details AS pd ON pu.id = pd.purchaseorder_id
                    WHERE pd.sku
                    IN (
                    $sku_can_str
                    ) and pd.id not in($sku_first_ids)
                    AND pu.`purchaseorder_date` <= '2014-12-31 23:59:59' ORDER BY pu.`purchaseorder_date` desc) temp
                    group by temp.sku";
                    $sku_second = DB::query(Database::SELECT,$sql)->execute('newerp')->as_array('sku');
                }

                if(isset($sku_first)&&isset($sku_second))
                {
                    $re = DB::insert('hzr')->columns(array('sku','diff','pu_date','wa_date'));
                    $flag_insert = 0;
                    foreach($sku_first as $sku_f)
                    {
                        if(isset($sku_second[$sku_f['sku']]['single_price']))
                        {
                            if($sku_second[$sku_f['sku']]['single_price']>$sku_f['single_price'])
                            {
                                //加入差值
                                $diff = $sku_second[$sku_f['sku']]['single_price']-$sku_f['single_price'];

                                $wa_date_sql="SELECT warehouse_date from warehouseorders
                                    where sku='".$sku_f['sku']."'
                                    and purchaseorders_id=".$sku_f['purchaseorder_id']."
                                    order by warehouse_date desc limit 1";
                                $wa_date = DB::query(Database::SELECT,$wa_date_sql)->execute('newerp')->current();
                                if($wa_date)
                                {
                                    $re->values(array($sku_f['sku'],$diff,$sku_f['purchaseorder_date'],$wa_date['warehouse_date']));
                                }
                                else
                                {
                                    $re->values(array($sku_f['sku'],$diff,$sku_f['purchaseorder_date'],''));
                                }
                                $flag_insert = 1;
                            }
                        }
                    }
                    if($flag_insert)
                        $re->execute('newerp');
                }
            }
        }
    }
}
